{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Running Presto SQL Queries from a Nuclio Function\n",
    "\n",
    "This notebook demonstrates how to create and test a Nuclio function that reads data via Presto"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# nuclio: ignore\n",
    "import nuclio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"read-presto-init\"></a>\n",
    "## Initialize Nuclio Emulation, Environment Variables, and Configuration\n",
    "\n",
    "> **Note:** Use `# nuclio: ignore` for sections that don't need to be copied to the function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# nuclio: ignore\n",
    "# copy the secret files neeeded for Presto to v3io directory\n",
    "# The secrete files are used to store the credentials for the presto session  \n",
    "\n",
    "!mkdir -p /v3io/${V3IO_HOME}/secrets\n",
    "!cp /var/run/iguazio/secrets/* /v3io/${V3IO_HOME}/secrets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "%nuclio env -c DATABASE_URL=${DATABASE_URL}\n",
    "%nuclio env -c V3IO_USERNAME=${V3IO_USERNAME}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%nuclio cmd -c\n",
    "pip install pandas==1.0.1\n",
    "pip install requests==2.23.0\n",
    "pip install git+https://github.com/v3io/PyHive.git@v0.6.999 # Pyhive version with bug fixes\n",
    "pip install sqlalchemy==1.3.14"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "%nuclio: setting spec.build.baseImage to 'python:3.6-jessie'\n"
     ]
    }
   ],
   "source": [
    "%%nuclio config \n",
    "spec.build.baseImage = \"python:3.6-jessie\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mounting volume path /var/run/iguazio/secrets as ~/secrets\n"
     ]
    }
   ],
   "source": [
    "# Mount secrets as /var/run/iguazio/secrets - this is the location where DATABASE_URL is configured to read from\n",
    "%nuclio mount /var/run/iguazio/secrets ~/secrets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import os \n",
    "from sqlalchemy.engine import create_engine \n",
    "import pyhive"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"read-presto-Handler\"></a>\n",
    "## Function Handler - connect to Presto and use SQL to read data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def handler(context, event):\n",
    "    \n",
    "    mnemonic = event.body.decode('utf-8').strip()\n",
    "    context.logger.info(mnemonic)\n",
    "    \n",
    "    # DATABASE_URL contains the Presto URL, as well as access key and location of secrets\n",
    "    engine = create_engine (os.getenv('DATABASE_URL'))\n",
    "\n",
    "    # note - make sure to create and popultate the stocks_tab table in advance - (check the getting started section in the collect-n-explore notebook)\n",
    "    table_path = os.path.join('v3io.users.\"'+str(os.getenv('V3IO_USERNAME'))+'/examples/stocks_tab\"')\n",
    "    query = 'select max(endprice) endprice from '+table_path+\"  where mnemonic = '\"+mnemonic+\"'\"\n",
    "    context.logger.info(query)\n",
    "\n",
    "    df = pd.read_sql(query,engine)\n",
    "    return (df.loc[0,'endprice'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"read-presto-trigger\"></a>\n",
    "## Trigger the Function - test locally"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Python> 2019-06-11 11:14:27,404 [info] BAYN\n",
      "Python> 2019-06-11 11:14:27,474 [info] select max(endprice) endprice from v3io.users.\"iguazio/examples/stocks_tab\"  where mnemonic = 'BAYN'\n",
      "90.85\n"
     ]
    }
   ],
   "source": [
    "# nuclio: ignore\n",
    "# note - make sure to popultate the stocks_tab table by running getting started collect-n-explore section\n",
    "stock = 'BAYN' \n",
    "\n",
    "event = nuclio.Event(body=bytes(stock, 'utf-8'))\n",
    "output = handler(context, event)\n",
    "print(output)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "%nuclio: notebook read-from-presto exported\n",
      "Config:\n",
      "apiVersion: nuclio.io/v1\n",
      "kind: Function\n",
      "metadata:\n",
      "  annotations:\n",
      "    nuclio.io/generated_by: function generated at 11-06-2019 by iguazio from /User/read-from-presto.ipynb\n",
      "  labels: {}\n",
      "  name: read-from-presto\n",
      "spec:\n",
      "  build:\n",
      "    baseImage: python:3.6-jessie\n",
      "    commands:\n",
      "    - pip install pandas\n",
      "    - pip install requests\n",
      "    - 'pip install git+https://github.com/v3io/PyHive.git@v0.6.999 # Pyhive version\n",
      "      with bug fixes'\n",
      "    - pip install sqlalchemy\n",
      "    functionSourceCode: IyBHZW5lcmF0ZWQgYnkgbnVjbGlvLmV4cG9ydC5OdWNsaW9FeHBvcnRlciBvbiAyMDE5LTA2LTExIDExOjE0CgppbXBvcnQgcGFuZGFzIGFzIHBkIAppbXBvcnQgb3MgCmZyb20gc3FsYWxjaGVteS5lbmdpbmUgaW1wb3J0IGNyZWF0ZV9lbmdpbmUgCmltcG9ydCBweWhpdmUKCmRlZiBoYW5kbGVyKGNvbnRleHQsIGV2ZW50KToKICAgIAogICAgbW5lbW9uaWMgPSBldmVudC5ib2R5LmRlY29kZSgndXRmLTgnKS5zdHJpcCgpCiAgICBjb250ZXh0LmxvZ2dlci5pbmZvKG1uZW1vbmljKQogICAgCiAgICBlbmdpbmUgPSBjcmVhdGVfZW5naW5lIChvcy5nZXRlbnYoJ0RBVEFCQVNFX1VSTCcpKQoKICAgIHRhYmxlX3BhdGggPSBvcy5wYXRoLmpvaW4oJ3YzaW8udXNlcnMuIicrc3RyKG9zLmdldGVudignVjNJT19VU0VSTkFNRScpKSsnL2V4YW1wbGVzL3N0b2Nrc190YWIiJykKICAgIHF1ZXJ5ID0gJ3NlbGVjdCBtYXgoZW5kcHJpY2UpIGVuZHByaWNlIGZyb20gJyt0YWJsZV9wYXRoKyIgIHdoZXJlIG1uZW1vbmljID0gJyIrbW5lbW9uaWMrIiciCiAgICBjb250ZXh0LmxvZ2dlci5pbmZvKHF1ZXJ5KQoKICAgIGRmID0gcGQucmVhZF9zcWwocXVlcnksZW5naW5lKQogICAgcmV0dXJuIChkZi5sb2NbMCwnZW5kcHJpY2UnXSkKCg==\n",
      "    noBaseImagesPull: true\n",
      "  env:\n",
      "  - name: DATABASE_URL\n",
      "    value: presto://iguazio:b60103ac-53c5-4322-98f5-75ff55984d87@presto-api-presto.default-tenant.app.hive2.iguazio-cd2.com:443/v3io?protocol=https&requests_kwargs=%7B%22verify%22%3A+%22%2Fvar%2Frun%2Figuazio%2Fsecrets%2Ftls.crt%22%2C+%22cert%22%3A+%5B%22%2Fvar%2Frun%2Figuazio%2Fsecrets%2Ftls.crt%22%2C+%22%2Fvar%2Frun%2Figuazio%2Fsecrets%2Ftls.key%22%5D%7D\n",
      "  - name: V3IO_USERNAME\n",
      "    value: iguazio\n",
      "  handler: read-from-presto:handler\n",
      "  runtime: python:3.6\n",
      "  volumes:\n",
      "  - volume:\n",
      "      flexVolume:\n",
      "        driver: v3io/fuse\n",
      "        options:\n",
      "          accessKey: b60103ac-53c5-4322-98f5-75ff55984d87\n",
      "          container: users\n",
      "          subPath: /iguazio/secrets\n",
      "      name: fs\n",
      "    volumeMount:\n",
      "      mountPath: /var/run/iguazio/secrets\n",
      "      name: fs\n",
      "\n",
      "Code:\n",
      "# Generated by nuclio.export.NuclioExporter on 2019-06-11 11:14\n",
      "\n",
      "import pandas as pd \n",
      "import os \n",
      "from sqlalchemy.engine import create_engine \n",
      "import pyhive\n",
      "\n",
      "def handler(context, event):\n",
      "    \n",
      "    mnemonic = event.body.decode('utf-8').strip()\n",
      "    context.logger.info(mnemonic)\n",
      "    \n",
      "    engine = create_engine (os.getenv('DATABASE_URL'))\n",
      "\n",
      "    table_path = os.path.join('v3io.users.\"'+str(os.getenv('V3IO_USERNAME'))+'/examples/stocks_tab\"')\n",
      "    query = 'select max(endprice) endprice from '+table_path+\"  where mnemonic = '\"+mnemonic+\"'\"\n",
      "    context.logger.info(query)\n",
      "\n",
      "    df = pd.read_sql(query,engine)\n",
      "    return (df.loc[0,'endprice'])\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "%nuclio show"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"read-presto-deploy\"></a>\n",
    "## Deploy the Function\n",
    "\n",
    "Run the following command to deploy the function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[nuclio.deploy] 2019-06-11 11:14:35,358 (info) Building processor image\n",
      "[nuclio.deploy] 2019-06-11 11:14:53,512 (info) Pushing image\n",
      "[nuclio.deploy] 2019-06-11 11:14:54,520 (info) Build complete\n",
      "[nuclio.deploy] 2019-06-11 11:14:58,555 (info) Function deploy complete\n",
      "[nuclio.deploy] 2019-06-11 11:14:58,561 done updating read-from-presto, function address: 52.58.191.99:31618\n",
      "%nuclio: function deployed\n"
     ]
    }
   ],
   "source": [
    "%nuclio deploy -n read-from-presto -p examples -c"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"read-presto-test\"></a>\n",
    "## Test the Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# nuclio: ignore\n",
    "# test the new API end point, take the address from the deploy log above\n",
    "!curl -X post -d \"BAYN\" URL:PORT"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
